﻿using Fdb.Model;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Fdb.SqlServer
{
    /// <summary>
    ///  数据库操作类(数据层)
    /// </summary>
    internal class Manager<T> : DBHandler<T>
    {
        #region 连接字符串

        private SqlDbHelper _SqlDbHelper = new SqlDbHelper();

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="connectionString"></param>
        public Manager(string connectionString, DatabaseType databaseType) : base(databaseType)
        {
            _SqlDbHelper.ConnectionString = connectionString;
        }

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="connectionString"></param>
        public Manager(string connectionString, DatabaseType databaseType, DbTransaction dbTransaction) : base(databaseType, dbTransaction)
        {
            _SqlDbHelper.ConnectionString = connectionString;
        }

        #endregion 连接字符串

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="dbname"></param>
        public override ExecuteResult CreateDB(string connectionString, string dbname)
        {
            ExecuteResult er = new ExecuteResult();
            return er;
        }

        #region 获取数据库结构抽象方法

        /// <summary>
        /// 获得sql版本号
        /// </summary>
        /// <returns></returns>
        public string GetSQLVer()
        {
            string version = this.GetDataObject("select @@VERSION").ToString();
            if (version.IndexOf("2000") >= 0) return "2000";
            if (version.IndexOf("2005") >= 0) return "2005";
            if (version.IndexOf("2008") >= 0) return "2008";
            return version;
        }

        /// <summary>
        /// 获取数据表结构
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public DataTable GetOleDbSchemaTable()
        {
            string version = this.GetDataObject("select @@VERSION").ToString();
            string sql = (version.IndexOf("2005") >= 0 || version.IndexOf("2008") >= 0) ? SqlServerUtil.getTableSQL_2005() : SqlServerUtil.getTableSQL_2000();
            DataTable dt = this.GetDataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定数据库下所有表信息
        /// </summary>
        /// <param name="dbname">数据库名称</param>
        /// <returns></returns>
        public override List<TableClass> GetDbTableList(string dbname)
        {
            List<TableClass> tablelist = new List<TableClass>();

            string version = this.GetDataObject("select @@VERSION").ToString();
            string sql = (version.IndexOf("2005") >= 0 || version.IndexOf("2008") >= 0) ? SqlServerUtil.getDatabaseSQL_2005() : SqlServerUtil.getDatabaseSQL_2000();
            DataTable dt = this.GetDataTable(sql);

            foreach (DataRow dr in dt.Rows)
            {
                TableClass tbl = new TableClass();
                tbl.TableName = dr["表名"].ToString();
                tbl.TableCNName = dr["表说明"].ToString();
                tablelist.Add(tbl);
            }

            return tablelist;
        }

        /// <summary>
        /// 获取指定数据库下指定表的所有字段信息
        /// </summary>
        /// <param name="dbname">数据库名称</param>
        /// <param name="tblname">表名称</param>
        /// <returns></returns>
        public override List<FieldClass> GetTblFieldList(string dbname, string tblname)
        {
            string version = this.GetDataObject("select @@VERSION").ToString();
            string sql = (version.IndexOf("2005") >= 0 || version.IndexOf("2008") >= 0) ? SqlServerUtil.getTableSQL_2005() : SqlServerUtil.getTableSQL_2000();
            DataTable dt = this.GetDataTable(sql);

            DataRow[] allFields = dt.Select("表名='" + tblname + "'");
            List<FieldClass> list = new List<FieldClass>();

            foreach (DataRow dr in allFields)
            {
                FieldClass field = new FieldClass();
                field.TableType = "SqlServer";
                if (dr["标识"].ToString().Equals("√")) field.IsPrimaryKey = true;
                field.TableCNName = dr["表说明"].ToString();
                field.TableName = dr["表名"].ToString();
                field.FieldCNName = dr["字段说明"].ToString();
                field.FieldName = dr["字段名"].ToString();
                field.FieldLength = Convert.ToInt32(dr["长度"].ToString());
                field.Nullable = dr["允许空"].ToString().Equals("√") ? true : false;
                field.FieldTypeName = dr["类型"].ToString();

                string column_type = field.FieldTypeName.ToLower();
                if (column_type.IndexOf("int") > -1 && column_type.IndexOf("big") < 0)
                {
                    field.FieldTypeName = "int"; if (!field.IsPrimaryKey) field.FieldTypeName += "?";
                }
                else if (column_type.IndexOf("bigint") > -1)
                {
                    field.FieldTypeName = "long"; if (!field.IsPrimaryKey) field.FieldTypeName += "?";
                }
                else if (column_type.IndexOf("decimal") > -1)
                {
                    field.FieldTypeName = "decimal"; if (!field.IsPrimaryKey) field.FieldTypeName += "?";//XXX：不能用double，否则无法转换
                }
                else if (column_type.IndexOf("numeric") > -1)
                {
                    field.FieldTypeName = "decimal"; if (!field.IsPrimaryKey) field.FieldTypeName += "?";
                }
                else if (column_type.IndexOf("varchar") > -1)
                {
                    field.FieldTypeName = "string";
                }
                else if (column_type.IndexOf("text") > -1)
                {
                    field.FieldTypeName = "string";
                }
                else if (column_type.IndexOf("char") > -1)
                {
                    field.FieldTypeName = "string";
                }
                else if (column_type.IndexOf("date") > -1)
                {
                    field.FieldTypeName = "DateTime"; field.FieldTypeName += "?";
                }
                else if (column_type.IndexOf("image") > -1)
                {
                    field.FieldTypeName = "System.Drawing.Image";
                }
                else if (column_type.IndexOf("bit") > -1)
                {
                    field.FieldTypeName = "int"; if (!field.IsPrimaryKey) field.FieldTypeName += "?";
                }

                list.Add(field);
            }

            return list;
        }

        /// <summary>
        /// 获取字段属性列表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<FieldClass> GetFieldList(string tableName)
        {
            DataTable table = GetOleDbSchemaTable();
            DataRow[] allFields = table.Select("表名='" + tableName + "'");
            List<FieldClass> list = new List<FieldClass>();

            foreach (DataRow dr in allFields)
            {
                FieldClass field = new FieldClass();
                field.TableType = "SqlServer";
                field.TableCNName = dr["表说明"].ToString();
                field.TableName = dr["表名"].ToString();
                field.FieldCNName = dr["字段说明"].ToString();
                field.FieldName = dr["字段名"].ToString();
                field.FieldTypeName = dr["类型"].ToString();
                field.FieldLength = Convert.ToInt32(dr["长度"].ToString()); ;
                field.Nullable = dr["允许空"].ToString().Equals("√") ? true : false;

                if (dr["标识"].ToString().Equals("√")) field.IsPrimaryKey = true;
                list.Add(field);
            }

            return list;
        }

        #endregion 获取数据库结构抽象方法

        #region 表维护抽象方法

        /// <summary>
        /// 创建一个默认数据表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override ExecuteResult CreateTable(string tableName)
        {
            string sql = "Create TABLE " + tableName + "(";
            sql += "ID INT not null auto_increment primary key";
            sql += ")";
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 修改表名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override ExecuteResult ModifyTable(string oldTableName, string newTableName)
        {
            string sql = "ALTER  TABLE " + oldTableName + " RENAME TO " + newTableName;
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 新增字段
        /// </summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public override ExecuteResult CreateField(FieldClass field)
        {
            string sql = string.Empty;
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 删除字段
        /// </summary>
        /// <param name="field">field</param>
        /// <returns></returns>
        public override ExecuteResult DeleteField(FieldClass field)
        {
            string sql = "ALTER TABLE " + field.TableName + " DROP COLUMN " + field.FieldName;
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 修改表名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override ExecuteResult ModifyField(FieldClass oldField, FieldClass newField)
        {
            string sql = string.Empty;
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 根据实体类创建表
        /// </summary>
        /// <returns></returns>
        public override ExecuteResult CreateTable()
        {
            string sql = "Create TABLE [" + this._TableName + "](";
            string fields = string.Empty;
            PropertyInfo[] props = GetPropertyInfoArray();
            foreach (PropertyInfo proper in props)
            {
                sql += "[" + proper.Name + "]";
                string fType = string.Empty;
                string type = proper.PropertyType.ToString();

                switch (type)
                {
                    case "System.Int64": fType = "INT"; break;
                    case "System.Int32": fType = "INT"; break;
                    case "System.String": fType = "VARCHAR(200)"; break;
                    case "System.DateTime": fType = "DATETIME"; break;
                    case "System.Double": fType = "DECIMAL"; break;
                }

                fType += " ";
                if (!string.IsNullOrEmpty(fields)) fType = "," + fType;

                fields += fType;

            }

            sql += fields;
            sql += ")";

            ExecuteResult er = this.ExecuteSQLWithResult(sql);

            return er;
        }

        /// <summary>
        ///  创建业务流程视图
        /// </summary>
        /// <param name="bizViewName"></param>
        /// <param name="primary"></param>
        /// <param name="datasouceSQL"></param>
        /// <param name="flowCode"></param>
        /// <returns></returns>
        public override ExecuteResult CreateBizFlowView(string flowCode, string bizViewName, string primary, string datasouceSQL)
        {
            ExecuteResult result = new ExecuteResult();
            string str = string.Empty;
            string str2 = "CREATE OR REPLACE VIEW " + bizViewName + " AS select t.*,isnull(o.FlowCode,'" + flowCode + "') as FlowCode,isnull(o.BizRecordId,t." + primary + ") as BizRecordId,isnull(o.BizRecordState,1) as BizRecordState,isnull(o.BizRecordState_Text,'<span style=\"color:green\">未申报</span>') as BizRecordState_Text,isnull(o.CurrentNodeLevel,0) as CurrentNodeLevel,o.ActorId,o.ActorName from ( " + datasouceSQL + ") t ";
            str = str2 + "left join (select * from WF_Current where FlowCode like '" + flowCode + "') o on t." + primary + "=o.BizRecordGuid";
            return this.ExecuteSQLWithResult(str);
        }

        #endregion 表维护抽象方法

        #region 基础方法

        /// <summary>
        /// 开启一个数据库事务对象
        /// </summary>
        /// <returns></returns>
        public override ExecuteResult BeginTransaction()
        {
            ExecuteResult er = new ExecuteResult();
            SqlConnection conn = new SqlConnection(_SqlDbHelper.ConnectionString);

            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                er.ResultString = "打开数据库连接失败！- " + ex.ToString();
                er.Success = false;
                return er;
            }

            if (conn.State == ConnectionState.Open)
            {
                try
                {
                    er.DbTransaction = conn.BeginTransaction();
                    er.DbConnection = conn;
                }
                catch (Exception ex)
                {
                    er.ResultString = "开启事务失败！- " + ex.ToString();
                    er.Success = false;
                    return er;
                }

                return er;
            }
            else
            {
                er.ResultString = "打开连接失败！";
                er.Success = false;
                return er;
            }
        }

        /// <summary>
        /// 测试连接
        /// </summary>
        /// <returns></returns>
        public override ExecuteResult TestConnnection()
        {
            ExecuteResult er = new ExecuteResult();
            SqlConnection conn = new SqlConnection(_SqlDbHelper.ConnectionString);

            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                er.ResultString = "打开数据库连接失败！- " + ex.ToString();
                er.Success = false;
                return er;
            }

            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                return er;
            }
            else
            {
                er.ResultString = "打开连接失败！";
                er.Success = false;
                return er;
            }
        }

        /// <summary>
        /// 获得datarow
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override DataRow GetDataRow(string sql, NHashtable ht)
        {
            DataTable dt = new DataTable();

            try
            {
                dt = GetDataTable(sql, ht);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt.Rows.Count == 0 ? null : dt.Rows[0];
        }

        /// <summary>
        /// 获得datarow
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DataRow GetDataRow(string sql)
        {
            DataTable dt = new DataTable();

            try
            {
                dt = GetDataTable(sql, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt.Rows.Count == 0 ? null : dt.Rows[0];
        }

        /// <summary>
        /// 获得DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DataTable GetDataTable(string sql)
        {
            DataSet dataSet = new DataSet();

            try
            {
                _SqlDbHelper.ExecuteSQL(sql, ref dataSet);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dataSet.Tables[0];
        }

        /// <summary>
        /// 获得DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override DataTable GetDataTable(string sql, NHashtable ht)
        {
            DataSet dataSet = new DataSet();
            SqlParameter[] parlist = this.GetDbParameterArray(ht);

            try
            {
                _SqlDbHelper.GetDataSet(sql, parlist, ref dataSet);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dataSet.Tables[0];
        }

        /// <summary>
        /// 根据存储过程获取DataTable对象
        /// </summary>
        /// <param name="storeProcName">存储过程名称</param>
        /// <returns></returns>
        public override DataTable GetDataTableWithStoreProc(string storeProcName)
        {
            return GetDataTableWithStoreProc(storeProcName, null);
        }

        /// <summary>
        /// 根据存储过程获取DataTable对象
        /// </summary>
        /// <param name="storeProcName">存储过程名称</param>
        /// <param name="ht">参数</param>
        /// <returns></returns>
        public override DataTable GetDataTableWithStoreProc(string storeProcName, NHashtable ht)
        {
            DataSet dataSet = new DataSet();
            SqlParameter[] parlist = this.GetDbParameterArray(ht);

            try
            {
                dataSet = _SqlDbHelper.RunProcedureGetDataSet(storeProcName, parlist);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dataSet.Tables[0];
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override ExecuteResult ExecuteSQLWithResult(string sql)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                _SqlDbHelper.ExecuteSQL(sql);
            }
            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override ExecuteResult ExecuteSQLWithConn(string conn, string sql)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                _SqlDbHelper.ConnectionString = conn;
                _SqlDbHelper.ExecuteSQL(sql);
            }

            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 执行带参数的sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override ExecuteResult ExecuteSQLWithResult(string sql, NHashtable ht)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                SqlParameter[] parlist = this.GetDbParameterArray(ht);
                if (this._DbTransaction == null)
                {
                    _SqlDbHelper.ExecuteSQL(sql, parlist);
                }
                else
                {
                    er.DbTransaction = this._DbTransaction;
                    er.DbConnection = this._DbTransaction.Connection;
                    _SqlDbHelper.ExcuteTransactionSQL(sql, parlist, this._DbTransaction);
                }
            }
            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override ExecuteResult ExcuteTransactionSQL(string sql, NHashtable ht)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                SqlParameter[] parlist = this.GetDbParameterArray(ht);
                long id = _SqlDbHelper.ExcuteTransactionSQL(sql, parlist);
                if (id > 0)
                {
                    er.ID = (int)id;
                }
            }

            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 获得单个object变量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override object GetDataObject(string sql)
        {

            try
            {
                object obj = GetDataObject(sql, new NHashtable());
                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// 获得单个object变量
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override object GetDataObject(string sql, NHashtable ht)
        {
            SqlParameter[] parlist = this.GetDbParameterArray(ht);

            try
            {
                object obj = _SqlDbHelper.GetSingle(sql, parlist);
                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取分页查询结果
        /// </summary>
        /// <param name="pageIndex">分页号</param>
        /// <returns></returns>
        public override DataTable GetDataTable(PagedQuery pagedQuery)
        {
            string sql = "select top " + pagedQuery.PageSize + " * from [" + pagedQuery.TableName + "]";
            string where = string.Empty;
            string orderby = "order by " + (string.IsNullOrEmpty(pagedQuery.SortClause) ? (pagedQuery.IdField + " asc") : pagedQuery.SortClause);

            if (pagedQuery.PageIndex > 1)
            {
                where = pagedQuery.IdField + " not in (select top " + (pagedQuery.PageIndex - 1) * pagedQuery.PageSize + " " + pagedQuery.IdField + " from [" + pagedQuery.TableName + "]";
                where += (string.IsNullOrEmpty(pagedQuery.WhereClause) ? "" : (" where " + pagedQuery.WhereClause));
                where += " " + orderby + ")";
            }

            if (!string.IsNullOrEmpty(pagedQuery.WhereClause))
            {
                if (!string.IsNullOrEmpty(where)) where += " and ";
                where += pagedQuery.WhereClause;
            }

            if (!string.IsNullOrEmpty(where)) where = " where " + where;
            sql += where + " " + orderby;

            DataTable dt = new DataTable();

            try
            {
                dt = this.GetDataTable(sql, pagedQuery.ParamList);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <returns></returns>
        public override int GetCount(PagedQuery pagedQuery)
        {
            string sql = "select Count(*) from [" + pagedQuery.TableName + "]" + (string.IsNullOrEmpty(pagedQuery.TMPWhereClause) ? string.Empty : (" where " + pagedQuery.TMPWhereClause));
            object o = null;

            try
            {
                o = this.GetDataObject(sql, pagedQuery.TMPParamList);
                return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion 基础方法

        #region 增删改方法

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="fieldTypes">所有字段类型值</param>
        /// <param name="fieldSizes">所有字段长度值</param>
        /// <returns></returns>
        public override ExecuteResult Insert(string tablename, NHashtable ht)
        {
            string sql = "insert into [" + tablename + "](";
            string realSql = string.Empty;

            int i = 0;
            foreach (string str in ht.Keys)
            {
                sql += "[" + str + "]";
                if (i < ht.Count - 1) sql += ",";
                i++;
            }

            sql += ")";
            sql += " values(";
            realSql = sql;

            i = 0;
            string realVal = string.Empty;//真实值
            foreach (string str in ht.Keys)
            {
                sql += "@" + str;

                realVal = Common.GetRealVal(ht[str]);
                realSql += realVal;

                if (i < ht.Count - 1)
                {
                    sql += ",";
                    realSql += ",";
                }

                i++;
            }

            sql += ")";
            realSql += ")";

            ExecuteResult er = new ExecuteResult();

            try
            {
                er.Success = true;
                er = this.ExecuteSQLWithResult(sql, ht);
                er.IsAdd = true;
                er.Sql = realSql;
                if (er.Success)
                {
                    object id = this.GetDataObject("SELECT ident_current('" + tablename + "')"); //HACK：此处会存在并发冲突的问题吗？
                    er.ID = Common.ConvertToIntWithOutNull(id);
                }
            }
            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Insert(T t)
        {
            NHashtable nht = new NHashtable();
            nht = CreateContentValues(t);
            List<PropertyInfo> keyList = Common.GetDbKeyFieldList<T>();
            if (keyList.Count == 1)
            {
                PropertyInfo key = Common.GetFirstDbKeyField<T>();
                if (key != null)
                {
                    bool is_auto_identity = Common.IsUseWithDescription(key);//默认一个主键的话都是数据库自动填充
                    if (is_auto_identity)
                    {
                        nht.Remove(key.Name);//删除主键
                    }
                }
            }

            return this.Insert(this._TableName, nht);
        }

        /// <summary>
        /// 编辑数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="condition">条件</param>
        /// <param name="fieldTypes">所有字段类型</param>
        /// <param name="fieldSizes">所有字段大小</param>
        /// <param name="realWhere">实际where语句</param>
        /// <returns></returns>
        public ExecuteResult Update(string tableName, NHashtable ht, List<PropertyInfo> keyList, string where, string realWhere)
        {
            string sql = "update [" + tableName + "] set ";
            string realSql = sql;

            string setsql = "";
            string setrealsql = "";
            foreach (string str in ht.Keys)
            {
                if (keyList.Count(p => p.Name == str) > 0) //主键不参与编辑
                {
                    continue;
                }

                string n = "[" + str + "]=@" + str;
                string n2 = "[" + str + "]=" + Common.GetRealVal(ht[str]);

                if (!string.IsNullOrEmpty(setsql))
                {
                    n = "," + n;
                }

                if (!string.IsNullOrEmpty(setrealsql))
                {
                    n2 = "," + n2;
                }

                setsql += n;
                setrealsql += n2;
            }

            sql += setsql;
            realSql += setrealsql;

            sql += " " + where;
            if (string.IsNullOrEmpty(realWhere))
            {
                realSql = sql;
            }
            else
            {
                realSql += " " + realWhere;
            }

            ExecuteResult er = new ExecuteResult();

            try
            {
                er = this.ExecuteSQLWithResult(sql, ht);
                er.IsAdd = false;
                er.Sql = realSql;
            }
            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 编辑数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public override ExecuteResult Update(string tableName, NHashtable ht, string where)
        {
            return Update(tableName, ht, new List<PropertyInfo>(), where, string.Empty);
        }

        /// <summary>
        /// 编辑数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Update(T t)
        {
            NHashtable nht = new NHashtable();
            nht = CreateContentValues(t);

            List<PropertyInfo> keyList = Common.GetDbKeyFieldList<T>();
            if (keyList.Count == 0)
            {
                return new ExecuteResult { ResultString = "未找到主键！", Success = false };
            }

            foreach (var key in keyList)
            {
                nht.Remove(key.Name);                      //删除主键
            }

            string where = "";
            string realWhere = "";

            foreach (var key in keyList)
            {
                nht.Add(key.Name, key.GetValue(t, null));
                string n = key.Name + "=@" + key.Name;
                if (!string.IsNullOrEmpty(where))
                {
                    n = " and " + n;
                }

                where += n;
            }

            foreach (var key in keyList)
            {
                string n = key.Name + "=" + Common.GetRealVal(key.GetValue(t, null));
                if (!string.IsNullOrEmpty(realWhere))
                {
                    n = " and " + n;
                }

                realWhere += n;
            }

            ExecuteResult er = this.Update(this._TableName, nht, keyList, "Where " + where, "Where " + realWhere);
            return er;
        }

        /// <summary>
        /// 根据编号删除数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Delete(long id)
        {
            string sql = "Delete from " + this._TableName + " Where ID = " + id;
            return this.ExecuteSQLWithResult(sql, null);
        }

        /// <summary>
        /// 根据GUID删除数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Delete(Guid guid)
        {
            string sql = "Delete from " + this._TableName + " Where guid = '" + guid + "'";
            return this.ExecuteSQLWithResult(sql, null);
        }

        /// <summary>
        /// 删除指定对象
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Delete(T t)
        {
            string sql = "Delete from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where)) where = " where " + where;
            sql += where;
            return this.ExecuteSQLWithResult(sql, paramtable);
        }

        #endregion 增删改方法

        #region 参数构造方法

        /// <summary>
        /// 获得数据库参数数组
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        private SqlParameter[] GetDbParameterArray(NHashtable ht)
        {
            SqlParameter[] parlist = null;
            if (ht != null && ht.Count > 0)
            {
                parlist = new SqlParameter[ht.Count];
                int i = 0;

                foreach (string str in ht.Keys)
                {
                    SqlDbType sqlDbType = Common.ChangeToSqlserverType(ht[str]);
                    int size = ht[str] == null ? 0 : ht[str].ToString().Length;
                    object value = ht[str] == null ? DBNull.Value : ht[str];

                    if (sqlDbType == SqlDbType.Image)
                    {
                        if (value != DBNull.Value)
                        {
                            //byte[] buff = ImageHelper.ImageToBytes((Image)value);
                            //size = buff.Length;
                            //value = buff;
                        }
                    }

                    SqlParameter sqlDbParameter = _SqlDbHelper.CreateInParam("@" + str, sqlDbType, size, value);
                    parlist[i] = sqlDbParameter;
                    i++;
                }
            }

            return parlist;
        }

        #endregion 参数构造方法

        #region 泛型查询方法

        /// <summary>
        /// 根据编号获取单个实例
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override T Fetch(long id)
        {
            PropertyInfo key = Common.GetFirstDbKeyField<T>();
            return Fetch("select * from " + this._TableName + " where " + key.Name + "=" + id);
        }

        /// <summary>
        /// 根据GUID获取单个实例
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override T Fetch(Guid guid)
        {
            return Fetch("select * from " + this._TableName + " where guid='" + guid.ToString() + "'");
        }

        /// <summary>
        /// 根据sql获取实例对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override T Fetch(string sql)
        {
            return Fetch(sql, null);
        }

        /// <summary>
        /// 根据sql和查询条件获取实例
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="nht"></param>
        /// <returns></returns>
        public override T Fetch(string sql, NHashtable nht)
        {
            DataRow dr = this.GetDataRow(sql, nht);
            return Fetch(dr);
        }

        /// <summary>
        /// 根据实体对象获取列表
        /// </summary>
        /// <returns></returns>
        public override T Fetch(T t)
        {
            List<T> list = FetchList(t);
            if (list.Count > 0) return list[0];

            return default(T);
        }

        /// <summary>
        /// 获取全部实例对象列表
        /// </summary>
        /// <returns></returns>
        public override List<T> FetchList()
        {
            string sql = "select * from " + this._TableName;
            return FetchList(sql, null);
        }

        /// <summary>
        /// 根据sql获取实例对象列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override List<T> FetchList(string sql)
        {
            return FetchList(sql, null);
        }

        /// <summary>
        /// 根据sql和参数表获取实例对象列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override List<T> FetchList(string sql, NHashtable ht)
        {
            List<T> list = new List<T>();
            DataTable dt = this.GetDataTable(sql, ht);
            if (dt == null) return list;

            foreach (DataRow dr in dt.Rows)
            {
                T instance = Fetch(dr);
                list.Add(instance);
            }

            return list;
        }

        /// <summary>
        /// 获取全部实例对象列表
        /// </summary>
        /// <returns></returns>
        public override List<T> FetchList(T t, string whereClause)
        {
            string sql = "select * from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where))
            {
                where = " where " + where;
            }

            if (!string.IsNullOrEmpty(whereClause))
            {
                if (string.IsNullOrEmpty(where))
                {
                    where = " where ";
                }
                else
                {
                    where += " and ";
                }
            }

            where += whereClause;
            sql += where;
            return FetchList(sql, paramtable);
        }

        /// <summary>
        /// 获取全部实例对象列表
        /// </summary>
        /// <returns></returns>
        public override List<T> FetchList(T t)
        {
            string sql = "select * from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where)) where = " where " + where;
            sql += where;
            return FetchList(sql, paramtable);
        }

        /// <summary>
        /// 将DataRow数组转为List
        /// </summary>
        /// <param name="drs"></param>
        /// <returns></returns>
        public override List<T> FetchList(DataRow[] drs)
        {
            List<T> list = new List<T>();
            foreach (DataRow dr in drs)
            {
                T instance = Fetch(dr);
                list.Add(instance);
            }

            return list;
        }

        /// <summary>
        /// 将DataTable类型转为List
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private List<T> FetchList(DataTable dt)
        {
            List<T> list = new List<T>();
            if (dt == null) return list;

            foreach (DataRow dr in dt.Rows)
            {
                T instance = Fetch(dr);
                list.Add(instance);
            }

            return list;
        }

        /// <summary>
        /// 获取分页结果
        /// </summary>
        /// <param name="t"></param>
        /// <param name="pagedQuery"></param>
        /// <returns></returns>
        public override List<T> FetchPageList(T t, PagedQuery pagedQuery)
        {
            this.InitPagedQuery(t, pagedQuery);
            DataTable dt = GetDataTable(pagedQuery);
            return FetchList(dt);
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="t"></param>
        /// <param name="pagedQuery"></param>
        /// <returns></returns>
        public override int FetchCount(T t, PagedQuery pagedQuery)
        {
            this.InitPagedQuery(t, pagedQuery);
            int count = GetCount(pagedQuery);
            return count;
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override int FetchCount(T t)
        {
            string sql = "select Count(*) from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where)) where = " where " + where;
            sql += where;

            object obj = this.GetDataObject(sql, paramtable);
            int count = Common.ConvertToIntWithMinValue(obj);
            return count;
        }

        #endregion 泛型查询方法
    }
}
